建表：
0: jdbc:phoenix:> select * from my_schema.my_table;
+-----+--------------------------+------+
| ID  |           DATE           | SEX  |
+-----+--------------------------+------+
| 1   | 2020-03-31 00:00:00.000  | 男   |
| 2   | 2020-02-28 00:00:00.000  | 2    |
| 3   | 2020-02-28 00:00:00.000  | 2    |
+-----+--------------------------+------+
3 rows selected (0.102 seconds)


20个region
CREATE TABLE my_schema.my_table ( id BIGINT not null primary key, date VARCHAR,sex varchar(10)) salt_buckets = 20;

1个region
CREATE TABLE my_schema.my_table ( id BIGINT not null primary key, date VARCHAR,sex varchar(10)) ;

4个region  region:3+1=4
CREATE TABLE my_schema.my_table ( id BIGINT not null primary key, date VARCHAR,sex varchar(10)) split(1,2,3) ;【不会像盐表一样有随机数前缀】

建索引：

全局索引
CREATE INDEX my_index ON my_schema.my_table(sex,date);

创建索引
CREATE INDEX my_index ON my_schema.my_table(sex,date);

0: jdbc:phoenix:> CREATE INDEX my_index ON my_schema.my_table(sex,date);
3 rows affected (6.432 seconds)
0: jdbc:phoenix:> select * from my_schema.my_index;
+--------+-------------+------+
| 0:SEX  |   0:DATE    | :ID  |
+--------+-------------+------+
| 0      | 2020-03-11  | 4    |
| 2      | 2020-03-23  | 2    |
| 2      | 2020-03-20  | 3    |
| 1      | 2020-05-31  | 1    |
+--------+-------------+------+

删除索引
DROP INDEX my_index ON my_schema.my_table;

全表扫描 all:
走数据表：explain select * from ruozedata.testsalt;
走数据表：explain select /*+ INDEX(ruozedata.testsalt testsalt_idx) */ * from ruozedata.testsalt;【使用hint强制】

explain select date from my_schema.my_table where sex='2'; 范围扫描
走索引表：RANGE SCAN OVER MY_SCHEMA:MY_INDEX2 ['2'] 【如果建表默认一个region不加盐】
走索引表：RANGE SCAN OVER MY_SCHEMA:MY_INDEX2 [0,'2'] - [19,'2'] 【如果建表设定20个region】

explain select sex from my_schema.my_table where date='2020-05-31'; 
走索引表：FULL SCAN OVER MY_SCHEMA.MY_INDEX 【如果建表默认一个region不加盐】全扫描
走索引表：RANGE SCAN OVER MY_SCHEMA:MY_INDEX [0] - [19] 【如果建表设定20个region】

alter table my_schema.my_table add col4 varchar(10);

upsert into my_schema.my_table(id, date,sex,col4) values (1,'2020-03-31','1','5');

走数据表：explain select sex from my_schema.my_table where col4='5';
走数据表：explain select date,col4 from my_schema.my_table where sex='5';
先走索引表查出rowkey再走数据表：explain select /*+ INDEX(my_schema.my_table my_index) */ date,col4 from my_schema.my_table where sex='5';

创建索引
CREATE LOCAL INDEX my_index ON my_schema.my_table(sex,date);

删除索引
DROP INDEX my_index ON my_schema.my_table;

全表扫描 all:
explain select * from my_schema.my_table;
explain select /*+ INDEX(my_schema.my_table my_index) */ * from ruozedata.testsalt;

走index，RANGE SCAN
explain select date from my_schema.my_table where sex=1;
走index，RANGE SCAN
explain select date,col4 from my_schema.my_table where sex=1;


走index，RANGE SCAN
explain select col4 from my_schema.my_table where sex=1;

强制，走index
explain select /*+ INDEX(my_schema.my_table my_index) */ col4 from my_schema.my_table where sex=1;

全表扫描
explain select /*+ INDEX(my_schema.my_table my_index) */ col4 from my_schema.my_table ;